# _*_ coding:utf-8 _*_
# @Time  : 2022.08.03
# @Author: zizlee
# 套利分析
import datetime

import pandas as pd
from collections import OrderedDict
from fastapi import APIRouter, Query
from db import FAConnection
from v1_all_api.all_utils import datetime_utils
from v1_all_api.all_response import AllResponse

arbitrage_api = APIRouter()


@arbitrage_api.get('/priceDiff/')  # 合约价差分析
async def contract_price_diff(c1: str = Query(...), c2: str = Query(...)):
    sql = """
        SELECT a.quotes_ts,a.contract As c1,b.contract As c2,a.close_price As p1,b.close_price As p2,
            a.close_price-b.close_price As pdiff 
        FROM 
        (SELECT quotes_ts,contract,close_price 
        FROM dat_futures_daily_quotes 
        WHERE contract=%s AND close_price>0) As a 
        INNER JOIN 
        (SELECT quotes_ts,contract,close_price 
        FROM dat_futures_daily_quotes 
        WHERE contract=%s AND close_price>0) As b 
        ON a.quotes_ts=b.quotes_ts;
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=[c1, c2])
    records.sort(key=lambda x: x['quotes_ts'])
    for r in records:
        r['quote_date'] = datetime_utils.timestamp_formatter(r['quotes_ts'], f='%Y%m%d')
    return AllResponse.operate_successfully(data=records)


@arbitrage_api.get('/priceDiffSeason/')  # 价差季节图形
def season_price_diff(v1: str = Query(...), v2: str = Query(...), m1: str = Query(...), m2: str = Query(...),
                      y: int = Query(3)):
    if v1 == v2 and m1 == m2:
        return AllResponse.validate_error(msg='相同月份合约不建议计算价差！价差为0')
    current_year = datetime_utils.today(obj=True).year
    start = datetime_utils.auth_datetime_string(f'{current_year - y}0101', f='%Y%m%d', ts=True)

    sql = """
        SELECT quotes_ts,contract,close_price 
        FROM dat_futures_daily_quotes 
        WHERE quotes_ts>=%s AND variety_en=%s AND close_price>0 AND RIGHT(contract,2)=%s 
        UNION
        SELECT quotes_ts,contract,close_price 
        FROM dat_futures_daily_quotes 
        WHERE quotes_ts>=%s AND variety_en=%s AND close_price>0 AND RIGHT(contract,2)=%s
    """
    db_conn = FAConnection()
    records = db_conn.query(sql, param=(start, v1, m1, start, v2, m2))
    df = pd.DataFrame(records)
    contract_df1 = df[(df['contract'].str.startswith(v1)) & (df['contract'].str.endswith(m1))]
    contract_df2 = df[(df['contract'].str.startswith(v2)) & (df['contract'].str.endswith(m2))]

    contract_df1.rename(columns={'contract': 'c1', 'close_price': 'p1'}, inplace=True)
    contract_df2.rename(columns={'contract': 'c2', 'close_price': 'p2'}, inplace=True)

    contract_df = pd.merge(contract_df1, contract_df2, how='inner', on='quotes_ts')
    contract_df['qdate'] = contract_df['quotes_ts'].apply(lambda x: datetime_utils.timestamp_formatter(x, f='%Y%m%d'))
    contract_df['year'] = contract_df['quotes_ts'].apply(lambda x: int(datetime_utils.timestamp_formatter(x, f='%Y')))
    del contract_df['quotes_ts']
    group_list = list(contract_df.groupby(by=['c1', 'c2']).groups)
    # 先过滤出品种一致的
    group_list = list(filter(lambda x: x[0][:-4] == v1 and x[1][:-4] == v2, group_list))
    check_group = group_list[-1]
    is_same = check_group[0][-4:-2] == check_group[1][-4:-2]
    if is_same:
        group_list = list(filter(lambda x: x[0][-4:-2] == x[1][-4:-2], group_list))  # 年份合约相等的组
    else:
        group_list = list(filter(lambda x: x[0][-4:-2] != x[1][-4:-2], group_list))  # 年份合约不相等的组
    group_list.reverse()
    group_list.pop()

    value_data = OrderedDict()
    min_date = '20991231'
    max_date = '19700101'

    for group in group_list:
        k1, k2 = group
        v_df = contract_df[(contract_df['c1'] == k1) & (contract_df['c2'] == k2)]
        v_df.sort_values(by='qdate', inplace=True)
        v_df = v_df.tail(180)  # 只显示最后180的数据，否则数据真TM奇怪
        v_df.reset_index(drop=True, inplace=True)
        v_df['year_diff'] = v_df['year'] - v_df.loc[0, 'year']
        v_df['uni_date'] = v_df.apply(lambda x: str(2020 + x.year_diff) + x.qdate[-4:], axis=1)
        v_df['pdiff'] = (v_df['p1'] - v_df['p2']).round(2)
        cdf_min_date = v_df['uni_date'].min()
        cdf_max_date = v_df['uni_date'].max()
        if cdf_min_date < min_date:
            min_date = cdf_min_date
        if cdf_max_date > max_date:
            max_date = cdf_max_date
        value_data[f'{k1}-{k2}'] = v_df.to_dict(orient='records')

    x_axis_data = []
    start_date = datetime.datetime.strptime(min_date, '%Y%m%d')
    end_date = datetime.datetime.strptime(max_date, '%Y%m%d')
    while start_date < end_date:
        x_axis_data.append(start_date.strftime('%Y%m%d'))
        start_date += datetime.timedelta(days=1)
    response_data = {
        'title': f'{v1}{m1}合约与{v2}{m2}合约季节价差',
        'x_axis_data': x_axis_data,
        'value_data': value_data
    }
    return AllResponse.operate_successfully(data=response_data)
